using System;
using System.Data.SqlClient;
using IdeaDomain.DomainLayer.Entities;
using IdeaDomain.DomainLayer.RepositoryInterfaces;
using IdeaDomain.InfrastructureLayer.DataEntities;
using IdeaDomain.InfrastructureLayer.DataManagers;
using IdeaDomain.InfrastructureLayer.Translator;
using UtilityComponent.DataFactory;

namespace IdeaDomain.InfrastructureLayer.Repositories
{
    public class RowRepository : IRowRepository
    {
        private IDataAccess _dataAccess;
        private DataManager _dataManger;
        private RowManager _rowManager;
        private readonly IdeaDomainModelDataEntities _mapper = new IdeaDomainModelDataEntities();

        public RowRepository()
        {
            _dataAccess = DataAccessFactory.CreateDataAccess();
            _dataManger = new DataManager();
            _rowManager = new RowManager();
        }

        /// <summary>
        /// Gets the row by id.
        /// </summary>
        /// <param name="rowId">The row id.</param>
        /// <param name="userId">The user id.</param>
        /// <returns></returns>
        public Row GetRowById(int rowId, int userId)
        {
            Row row = new Row();
            using (SqlConnection connection = _dataAccess.CreateConnection())
            {
                connection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = connection;
                row = _mapper.Map<Row>(_rowManager.GetRowById(cmd, rowId, userId));
                row.UserId = userId;
                connection.Close();
            }
            return row;
        }

        /// <summary>
        /// Adds the row.
        /// </summary>
        /// <param name="row">The row.</param>
        /// <returns></returns>
        public bool AddRow(Row row)
        {
            bool result = false;
            SqlConnection connection = _dataAccess.CreateConnection();
            connection.Open();
            SqlTransaction sqltrans = connection.BeginTransaction();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = connection;
            cmd.Transaction = sqltrans;
            try
            {
                int rowNumber = _rowManager.AddRow(cmd, _mapper.Map<RowDE>(row), row.UserId);
                for (int i = 0; i < row.Columns.Count; i++)
                {
                    ColumnInIdea column = row.Columns[i];
                    DataDE dataDE = new DataDE();
                    dataDE.RowId = rowNumber;
                    dataDE.ColumnId = column.ColumnId;
                    dataDE.IsDeleted = false;
                    dataDE.Value = row.Values[i];
                    _dataManger.AddData(cmd, dataDE, row.UserId, column.DataTypeId);
                }
                sqltrans.Commit();
                result = true;
            }
            catch
            {
                sqltrans.Rollback();
                result = false;
            }
            finally
            {
                connection.Close();
            }
            return result;
        }

        /// <summary>
        /// Updates the row.
        /// </summary>
        /// <param name="row">The row.</param>
        /// <returns></returns>
        public bool UpdateRow(Row row)
        {
            bool result = false;
            if (row != null && (row.Version == GetRowById(row.RowId, row.UserId).Version))
            {
                using (SqlConnection connection = _dataAccess.CreateConnection())
                {
                    connection.Open();
                    SqlTransaction sqltrans = connection.BeginTransaction();
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = connection;
                    cmd.Transaction = sqltrans;
                    ColumnInIdea column = new ColumnInIdea();
                    try
                    {
                        row.Version += 1;
                        _rowManager.UpdateRow(cmd, _mapper.Map<RowDE>(row), row.UserId);
                        for (int i = 0; i < row.Columns.Count; i++)
                        {
                            column = row.Columns[i];
                            DataDE dataDE = new DataDE();
                            dataDE.RowId = row.RowId;
                            dataDE.ColumnId = column.ColumnId;
                            dataDE.IsDeleted = false;
                            dataDE.Value = row.Values[i];
                            if (!_dataManger.UpdateData(cmd, dataDE, row.UserId, column.DataTypeId))
                            {
                                _dataManger.AddData(cmd, dataDE, row.UserId, column.DataTypeId);
                            }

                        }
                        sqltrans.Commit();
                        result = true;
                    }
                    catch
                    {
                        sqltrans.Rollback();
                        result = false;
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
            return result;
        }

        /// <summary>
        /// Deletes the row.
        /// </summary>
        /// <param name="rowID">The row ID.</param>
        /// <param name="userID">The user ID.</param>
        /// <returns></returns>
        public bool DeleteRow(int rowID, int userID)
        {
            bool result = false;
            using (SqlConnection connection = _dataAccess.CreateConnection())
            {
                connection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = connection;
                result = _rowManager.DeleteRow(cmd, rowID, userID);
                connection.Close();
            }
            return result;
        }

    }
}
